#include "db_class.h"
#include <QMessageBox>
#include<QDebug>
#include <QtSql/QSqlQuery>
//This class contains database realted functions
db_class::db_class(QObject *parent) :
    QObject(parent)
{
}
int db_class::db_connect()
{
    db=QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("finance_db");
    if(!db.open())
    {
        QMessageBox::information(0,"Error !","Error in opning database");
        return 0;
    }
    else
        return 1;

}
void db_class::db_close()
{
    db.close();
}
int db_class::db_account_table()
{

        QSqlQuery qry;
        qry.prepare("CREATE TABLE IF NOT EXISTS account_table(account_id integer primary key AUTOINCREMENT,name varchar(20) NOT NULL UNIQUE,account_amt real,budget_amt real,expense_amt real);");

        if(qry.exec())
                return 1;
        return 0;

}
int db_class::db_category_table()
{
    QSqlQuery qry;
    qry.prepare("CREATE TABLE IF NOT EXISTS category_table(category_name varchar(30) primary key);");
    if(qry.exec())
        return 1;
    else
        return 0;
}
int db_class::db_expense_table()
{
    QSqlQuery q;
    q.prepare(QString("CREATE TABLE IF NOT EXISTS expense_table(exp_id integer primary key AUTOINCREMENT,exp_amt real NOT NULL,note varchar(50),exp_date date not null,acc_id integer,cat_name varchar(30),foreign key(acc_id) references account_table(account_id),foreign key(cat_name) references category_table(category_name));"));
    if(q.exec())
        return 1;
    else
        return 0;

}

int db_class::db_account_insert(QString nm,double amt)
{
      QSqlQuery q;
      double budget=0;
      if(q.exec(QString("insert into account_table(name,account_amt,budget_amt,expense_amt) values('%1',%2,%3,%4)").arg(nm).arg(amt).arg(budget).arg(budget)))
           return 1;
      else
            return 0;

 }
int db_class::db_category_insert(QString cat)
{
    QSqlQuery q;
    if(q.exec(QString("insert into category_table(category_name) values('%1')").arg(cat)))
        return 1;
    else
        return 0;
}
/*int db_class::db_exp_insert(int acc,QString cat,double amt,QString note,QString dt)
{
    QSqlQuery q;
    if(q.exec(QString("insert into expense_table(exp_amt,note,exp_date,acc_id,cat_name) values(%1,'%2','%3',%4,'%5')").arg(amt).arg(note).arg(dt).arg(acc).arg(cat)))
        return 1;
    else
        return 0;
}
int db_class::db_exp_update(int expid, int accid, QString cat, double amt, QString note, QString dt)
{
    QSqlQuery q;
    if(q.exec(QString("update expense_table set acc_id=%1,cat_name='%2',exp_amt=%3,note='%4',exp_date='%5' where exp_id=%6").arg(accid).arg(cat).arg(amt).arg(note).arg(dt).arg(expid)))
        return 1;
    else
        return 0;
}*/

int db_class::budget_update(QString account,double new_budget)
{
    QSqlQuery q;
    if(q.exec(QString("update account_table set budget_amt=%1 where name='%2'").arg(new_budget).arg(account)))
        return 1;
    else
        return 0;
}
int db_class::db_account_update(QString accnm, double amt,int id)
{
    QSqlQuery q;
    if(q.exec(QString("update account_table set name='%1',account_amt=%2 where account_id=%3").arg(accnm).arg(amt).arg(id)))
        return 1;
    else
        return 0;
}

int db_class::db_category_update(QString old ,QString new1)
{
    QSqlQuery q;
    if(q.exec(QString("update category_table set category_name='%1' where category_name='%2'").arg(new1).arg(old)))
    {
        if(q.exec(QString("update expense_table set cat_name='%1' where cat_name='%2'").arg(new1).arg(old)))
            return 1;
    }
    return 0;
}
int db_class::db_category_delete(QString cat)
{
    QSqlQuery q;
    if(q.exec(QString("delete from category_table where category_name='%1'").arg(cat)))
    {
        QString name="Unknown";
        if(q.exec(QString("update expense_table set cat_name='%1' where cat_name='%2'").arg(name).arg(cat)))
        return 1;
    }
    return 0;
}
int db_class::db_account_delete(int id)
{
    QSqlQuery q;
    if(q.exec(QString("update expense_table set acc_id=%1 where acc_id=%2").arg(0).arg(id)))
    {
        if(q.exec(QString("delete from account_table where account_id=%1").arg(id)))
            return 1;
    }
    return 0;
}

int db_class::db_exp_delete(int expid)
{
    QSqlQuery q;
    if(q.exec(QString("delete from expense_table where exp_id=%1").arg(expid)))
        return 1;
    return 0;
}

